/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOImport;
import DTO.DTOInventory;
import DTO.DTOProduct;
import DTO.DTOStatistic;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALImport {
    ConnectionDB conn;
    DALProduct dalPro;
    DALInventory dalInv;
    
    public DALImport()
    {
        conn = new ConnectionDB();
        dalPro = new DALProduct();
        dalInv = new DALInventory();
    }
    
    //method import product
    public boolean importProduct(DTOImport dtoImp)
    {
        try{
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateupdate = df.format(dtoImp.getImportDate());
            
            //insert to table import
            String insertIm = String.format("insert into Import(import_quantity, import_price, import_date, product_id) values(%d, %d, '%s', %d)", dtoImp.getImportQuantity(), dtoImp.getImportPrice(), dateupdate, dtoImp.getDtoPro().getProductID());
            if(conn.exeUpdate(insertIm)){
                //insert new row to inventory with new quantity
                DTOInventory dtoInv = new DTOInventory();
                DTOInventory dtoCurrentInv = dalInv.getCurrentInventory(dtoImp.getDtoPro().getProductID());
                if(dtoCurrentInv != null)
                {
                    int newQuantity = dtoCurrentInv.getInventoryQuatity() + dtoImp.getImportQuantity();
                    dtoInv.setInventoryQuatity(newQuantity);
                }
                else
                    dtoInv.setInventoryQuatity(dtoImp.getImportQuantity());
                dtoInv.setInventoryDate(dtoImp.getImportDate());
                dtoInv.setDtoPro(dtoImp.getDtoPro());
                if(dalInv.insertRowInventory(dtoInv))
                {
                    DTOProduct dtoProductnewQuantity = dtoImp.getDtoPro();
                    dtoProductnewQuantity.setProductQuantity(dtoInv.getInventoryQuatity());
                    if(dalPro.updateProduct(dtoProductnewQuantity))//update quantity to table product
                        return true;
                }
//                String updatePro = String.format("update product set product_quantity = %d where product_id = %d", dtoImp.getImportQuantity(), dtoImp.getDtoPro().getProductID());
//                if(conn.exeUpdate(updatePro))
//                    return true;
            }
            return false;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method statistic import
    public ArrayList<DTOStatistic> statisticImport(String month, String year)
    {
        try
        {
            ArrayList<DTOStatistic> listObj = new ArrayList<>();
            String querySelect = "SELECT p.product_name, SUM( i.import_quantity ) AS  'quantity', i.import_price "
                                + "FROM  `import` i,  `product` p "
                                + "WHERE i.product_id = p.product_id AND MONTH(i.import_date) like '" + month + "' AND "
                                + "YEAR(i.import_date) like '" + year + "'"
                                + "GROUP BY p.product_name";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listObj.add(new DTOStatistic(rs));
            }
            return listObj;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }

    public ArrayList<DTOImport> getImports() {
        try{
            String querySelect = "select * from `import`";
            ArrayList<DTOImport> listImp = new ArrayList<>();
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listImp.add(new DTOImport(rs));
            }
            return listImp;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
}
